#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# type: ignore
"""
设备离线监控定时任务
功能：
1. 每15分钟执行一次
2. 检测设备最后上报时间
3. 超过15分钟无数据的在线设备 -> 标记为离线，创建告警和工单
4. 15分钟内有数据的离线设备 -> 恢复在线，关闭告警和工单
"""
import logging
import os
import sys
from datetime import datetime, timedelta
from typing import List, Dict, Any, Optional

import pymysql
import tenacity

# 添加项目根目录到 Python 路径
sys.path.insert(0, os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))

from common.base_enums import DeviceStatus, AlarmType, AlarmStatus, WorkOrderStatus, WorkOrderSourceType, \
	WorkOrderPriority
from common.config import settings

# ============ 日志配置 ============
# 确保日志目录存在
log_dir = os.path.join(os.path.dirname(__file__), '..', 'logs')
os.makedirs(log_dir, exist_ok=True)

logging.basicConfig(
	level=logging.INFO,
	format="%(asctime)s [%(levelname)s] %(message)s",
	handlers=[
		logging.StreamHandler(sys.stdout),  # 使用stdout，指定UTF-8编码
		logging.FileHandler(os.path.join(log_dir, "device_offline_monitor.log"), encoding='utf-8')
	]
)
logger = logging.getLogger(__name__)

# ============ 数据库配置（从统一配置对象获取） ============
DB_HOST = settings.db_host
DB_PORT = settings.db_port
DB_USER = settings.db_user
DB_PASSWORD = settings.db_password
DB_NAME = settings.db_name

# ============ 配置参数 ============
OFFLINE_THRESHOLD_MINUTES = 15  # 离线阈值：15分钟无数据
CHECK_WINDOW_MINUTES = 15  # 检查窗口：查询过去15分钟的数据
ALARM_TYPE_COMM_LOST = AlarmType.COMM_LOST.value  # 告警类型：通信中断
WORK_ORDER_TITLE_OFFLINE = "设备通信中断"
WORK_ORDER_TITLE_RECOVERED = "设备通信恢复"


class DeviceOfflineMonitor:
	"""设备离线监控类"""

	def __init__(self):
		self.conn: Optional[pymysql.Connection] = None
		self.processed_count = {
			"to_offline": 0,  # 变更为离线
			"to_online": 0,  # 恢复在线
			"skipped": 0,  # 跳过
			"error": 0  # 错误
		}

	def connect_db(self):
		"""连接数据库"""
		if not all([DB_HOST, DB_USER, DB_PASSWORD, DB_NAME]):
			raise ValueError("数据库配置不完整，请检查环境变量")

		self.conn = pymysql.connect(
			host=DB_HOST,
			port=DB_PORT,
			user=DB_USER,
			password=DB_PASSWORD,  # type: ignore
			database=DB_NAME,
			charset='utf8mb4',
			autocommit=False  # 手动提交事务
		)
		logger.info(f"数据库连接成功: {DB_HOST}:{DB_PORT}/{DB_NAME}")

	def close_db(self):
		"""关闭数据库连接"""
		if self.conn:
			self.conn.close()
			logger.info("数据库连接已关闭")

	def get_devices_with_last_telemetry(self) -> List[Dict[str, Any]]:
		"""
		查询所有RTU设备及其最后上报时间
		
		注意：仅监控RTU设备，不监控电表，因为：
		1. dc_meter_telemetry表存储的是RTU上报的数据
		2. RTU离线时无法判断电表是否故障
		3. 一个RTU关联多个电表，避免产生大量重复告警

		Returns:
			List[Dict]: RTU设备列表，包含 device_id, meter_code, status, device_type, last_ts, minutes_since_last
		"""
		assert self.conn is not None, "数据库未连接"
		sql = """
            SELECT
                v.device_id AS device_id,
                v.serial_no AS meter_code,
                v.status,
                v.device_type AS device_type,
                MAX(t.ts) AS last_ts,
                TIMESTAMPDIFF(MINUTE, MAX(t.ts), NOW()) AS minutes_since_last
            FROM v_all_device v
            LEFT JOIN dc_meter_telemetry t ON t.meter_code = v.serial_no
            WHERE v.device_type = 'RTU'
              AND v.status IN (%s, %s)
            GROUP BY v.device_id, v.serial_no, v.status, v.device_type
        """
		with self.conn.cursor(pymysql.cursors.DictCursor) as cursor:
			cursor.execute(sql, (
				DeviceStatus.ONLINE.value,
				DeviceStatus.OFFLINE.value
			))
			rows = cursor.fetchall()
			logger.info(f"查询到 {len(rows)} 个RTU设备")
			return list(rows)  # 转换为列表

	def check_alarm_exists(self, meter_code: str) -> bool:
		"""
		检查是否存在未关闭的通信中断告警

		Args:
			meter_code: 电表编码

		Returns:
			bool: 存在返回 True
		"""
		assert self.conn is not None
		sql = """
            SELECT 1 FROM am_alarm
            WHERE meter_code = %s
              AND alarm_type = %s
              AND status IN (%s, %s)
            LIMIT 1
        """
		with self.conn.cursor() as cursor:
			cursor.execute(sql, (
				meter_code,
				ALARM_TYPE_COMM_LOST,
				AlarmStatus.OPEN.value,
				AlarmStatus.ACKED.value
			))
			return cursor.fetchone() is not None

	def get_open_alarm_id(self, meter_code: str) -> Optional[int]:
		"""
		获取设备的未关闭告警ID

		Args:
			meter_code: 电表编码

		Returns:
			Optional[int]: 告警ID，不存在则返回 None
		"""
		sql = """
            SELECT id FROM am_alarm
            WHERE meter_code = %s
              AND alarm_type = %s
              AND status IN (%s, %s)
            ORDER BY raised_at DESC
            LIMIT 1
        """
		with self.conn.cursor() as cursor:
			cursor.execute(sql, (
				meter_code,
				ALARM_TYPE_COMM_LOST,
				AlarmStatus.OPEN.value,
				AlarmStatus.ACKED.value
			))
			row = cursor.fetchone()
			return row[0] if row else None

	def create_alarm_and_work_order(self, meter_code: str, device_type: str, device_id: int):
		"""
		创建告警和工单

		Args:
			meter_code: 电表编码
			device_type: 设备类型
			device_id: 设备ID
		"""
		try:
			# 1. 创建告警
			alarm_sql = """
                INSERT INTO am_alarm (meter_code, alarm_type, status, raised_at, updated_at, msg_id)
                VALUES (%s, %s, %s, NOW(), NOW(), %s)
            """
			msg_id = f"OFFLINE_MONITOR_{meter_code}_{datetime.now().strftime('%Y%m%d%H%M%S')}"
			with self.conn.cursor() as cursor:
				cursor.execute(alarm_sql, (
					meter_code,
					ALARM_TYPE_COMM_LOST,
					AlarmStatus.OPEN.value,
					msg_id
				))
				alarm_id = cursor.lastrowid
				logger.info(f"创建告警成功: alarm_id={alarm_id}, meter_code={meter_code}")

			# 2. 创建工单
			work_order_code = self._generate_work_order_code()
			work_order_sql = """
                INSERT INTO wo_work_order (
                    code, title, source_type, alarm_id, device_type, device_id,
                    priority, description, status, assignee_code, deleted,
                    created_at, updated_at
                ) VALUES (
                    %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW(), NOW()
                )
            """
			description = f"设备 {meter_code} 超过 {OFFLINE_THRESHOLD_MINUTES} 分钟未上报数据，系统自动创建工单"
			with self.conn.cursor() as cursor:
				cursor.execute(work_order_sql, (
					work_order_code,
					WORK_ORDER_TITLE_OFFLINE,
					WorkOrderSourceType.ALARM.value,
					alarm_id,
					device_type,
					device_id,  # 使用device_id而非meter_code
					WorkOrderPriority.HIGH.value,
					description,
					WorkOrderStatus.CREATED.value,
					None,  # assignee_code 待派单
					0  # deleted
				))
				work_order_id = cursor.lastrowid
				logger.info(f"创建工单成功: work_order_id={work_order_id}, code={work_order_code}")

			# 3. 插入告警工单映射表
			insert_mapping_sql = "INSERT INTO am_alarm_work (alarm_id, work_order_id) VALUES (%s, %s)"
			with self.conn.cursor() as cursor:
				cursor.execute(insert_mapping_sql, (alarm_id, work_order_id))

			# 4. 记录工单日志
			self._create_work_order_log(
				work_order_id,
				"CREATE",
				"SYSTEM",
				None,
				WorkOrderStatus.CREATED.value,
				"系统自动创建工单"
			)

			# 5. 记录告警确认日志
			self._create_alarm_ack_log(alarm_id, "SYSTEM", "告警产生")

		except Exception as e:
			import traceback
			logger.error(f"创建告警和工单失败: meter_code={meter_code}, error={e}")
			logger.error(f"详细错误堆栈:\n{traceback.format_exc()}")
			raise

	def close_alarm_and_work_order(self, meter_code: str, alarm_id: int):
		"""
		关闭告警和工单（自动恢复）

		Args:
			meter_code: 电表编码
			alarm_id: 告警ID
		"""
		try:
			# 0. 检查告警是否已经关闭，避免重复关闭
			check_sql = "SELECT status FROM am_alarm WHERE id = %s"
			with self.conn.cursor() as cursor:
				cursor.execute(check_sql, (alarm_id,))
				row = cursor.fetchone()
				if not row:
					logger.warning(f"告警不存在: alarm_id={alarm_id}")
					return
				current_status = row[0]
				if current_status == AlarmStatus.CLOSED.value:
					logger.info(f"告警已关闭，跳过: alarm_id={alarm_id}, meter_code={meter_code}")
					return

			# 1. 从映射表查询关联的工单ID
			query_sql = "SELECT work_order_id FROM am_alarm_work WHERE alarm_id = %s"
			with self.conn.cursor() as cursor:
				cursor.execute(query_sql, (alarm_id,))
				row = cursor.fetchone()
				work_order_id = row[0] if row and row[0] else None

			# 2. 更新告警状态为已关闭
			alarm_sql = """
                UPDATE am_alarm
                SET status = %s, close_reason = %s, updated_at = NOW()
                WHERE id = %s AND status != %s
            """
			with self.conn.cursor() as cursor:
				affected = cursor.execute(alarm_sql, (
					AlarmStatus.CLOSED.value,
					"NORMAL",  # AlarmCloseReason.NORMAL
					alarm_id,
					AlarmStatus.CLOSED.value  # 不更新已关闭的告警
				))
				if affected > 0:
					logger.info(f"关闭告警成功: alarm_id={alarm_id}, meter_code={meter_code}")
				else:
					logger.info(f"告警已是关闭状态，无需更新: alarm_id={alarm_id}")

			# 3. 如果有关联工单，也关闭工单
			if work_order_id:
				work_order_sql = """
                    UPDATE wo_work_order
                    SET status = %s, closed_by = %s, closed_at = NOW(),
                        close_comment = %s, updated_at = NOW()
                    WHERE id = %s
                """
				comment = f"设备 {meter_code} 通信恢复，系统自动关闭工单"
				with self.conn.cursor() as cursor:
					cursor.execute(work_order_sql, (
						WorkOrderStatus.CLOSED.value,
						"SYSTEM",
						comment,
						work_order_id
					))
					logger.info(f"关闭工单成功: work_order_id={work_order_id}")

				# 记录工单日志
				self._create_work_order_log(
					work_order_id,
					"CLOSE",
					"SYSTEM",
					WorkOrderStatus.CREATED.value,
					WorkOrderStatus.CLOSED.value,
					comment
				)

			# 4. 记录告警确认日志
			self._create_alarm_ack_log(alarm_id, "SYSTEM", "自动恢复")

		except Exception as e:
			logger.error(f"关闭告警和工单失败: meter_code={meter_code}, alarm_id={alarm_id}, error={e}")
			raise

	def update_device_status(self, meter_code: str, device_type: str, new_status: DeviceStatus):
		"""
		更新设备状态

		Args:
			meter_code: 电表编码
			device_type: 设备类型
			new_status: 新状态
		"""
		# 注意：v_all_device 是视图，需要更新源表
		sql = """
			UPDATE dev_meter
			SET status = %s, updated_at = NOW()
			WHERE serial_no = %s
		"""
		if device_type == "RTU":
			sql = """
				UPDATE dev_rtu
				SET status = %s, updated_at = NOW()
				WHERE serial_no = %s
			"""
		with self.conn.cursor() as cursor:
			affected = cursor.execute(sql, (new_status.value, meter_code))
			if affected > 0:
				logger.info(f"更新设备状态: meter_code={meter_code}, status={new_status.value}")
			else:
				logger.warning(f"设备状态更新失败，设备可能不存在: meter_code={meter_code}")

	def _generate_work_order_code(self) -> str:
		"""生成工单编码 W + YYYYMMDD + 4位流水号"""
		today = datetime.now().strftime("%Y%m%d")
		seq_sql = """
            SELECT MAX(CAST(SUBSTRING(code, 10) AS UNSIGNED)) as max_seq
            FROM wo_work_order
            WHERE code LIKE %s AND deleted = 0
        """
		with self.conn.cursor(pymysql.cursors.DictCursor) as cursor:
			cursor.execute(seq_sql, (f"W{today}%",))
			row = cursor.fetchone()
			max_seq = row["max_seq"] if row and row["max_seq"] else 0
			seq = str(max_seq + 1).zfill(4)
			return f"W{today}{seq}"

	def _create_work_order_log(
			self,
			work_order_id: int,
			action: str,
			operator: str,
			old_status: Optional[str],
			new_status: str,
			comment: str
	):
		"""记录工单日志"""
		sql = """
            INSERT INTO wo_work_order_log (
                wo_id, action, actor_code, old_status, new_status,
                comment, created_at
            ) VALUES (%s, %s, %s, %s, %s, %s, NOW())
        """
		with self.conn.cursor() as cursor:
			cursor.execute(sql, (
				work_order_id, action, operator, old_status, new_status, comment
			))

	def _create_alarm_ack_log(self, alarm_id: int, actor: str, action: str):
		"""记录告警确认日志"""
		sql = """
            INSERT INTO am_alarm_ack_log (alarm_id, actor, action, ack_at)
            VALUES (%s, %s, %s, NOW())
        """
		with self.conn.cursor() as cursor:
			cursor.execute(sql, (alarm_id, actor, action))

	def process_device(self, device: Dict[str, Any]):
		"""
		处理单个设备

		Args:
			device: 设备信息字典
		"""
		meter_code = device["meter_code"]
		current_status = device["status"]
		device_type = device["device_type"]
		device_id = device["device_id"]
		minutes_since_last = device["minutes_since_last"]

		try:
			# 情兵1: 设备从未上报数据（last_ts 为 NULL）
			if minutes_since_last is None:
				# 如果当前状态为在线，需要标记为离线
				if current_status == DeviceStatus.ONLINE.value:
					# 检查是否已有告警（避免重复创建）
					if self.check_alarm_exists(meter_code):
						logger.debug(f"设备已有未关闭告警，跳过: {meter_code}")
						self.processed_count["skipped"] += 1
						return

					logger.warning(f"设备从未上报数据，标记为离线: meter_code={meter_code}")

					# 开启事务
					try:
						self.update_device_status(meter_code, device_type, DeviceStatus.OFFLINE)
						self.create_alarm_and_work_order(meter_code, device_type, device_id)
						self.conn.commit()
					except Exception as e:
						self.conn.rollback()
						raise
					self.processed_count["to_offline"] += 1
					logger.info(f"设备已标记为离线: {meter_code}")
				else:
					# 已经是离线状态，跳过
					logger.debug(f"设备从未上报数据且已离线，跳过: {meter_code}")
					self.processed_count["skipped"] += 1
				return

			# 情况2: 在线设备超过15分钟无数据 -> 标记离线
			if current_status == DeviceStatus.ONLINE.value:
				if minutes_since_last >= OFFLINE_THRESHOLD_MINUTES:
					# 检查是否已有告警（避免重复创建）
					if self.check_alarm_exists(meter_code):
						logger.debug(f"设备已有未关闭告警，跳过: {meter_code}")
						self.processed_count["skipped"] += 1
						return

					logger.warning(f"设备离线: meter_code={meter_code}, "
								   f"最后上报={device['last_ts']}, "
								   f"距今={minutes_since_last}分钟")

					# 开启事务
					try:
						self.update_device_status(meter_code, device_type, DeviceStatus.OFFLINE)
						self.create_alarm_and_work_order(meter_code, device_type, device_id)
						self.conn.commit()
					except Exception as e:
						self.conn.rollback()
						raise
					self.processed_count["to_offline"] += 1
					logger.info(f"设备已标记为离线: {meter_code}")
				else:
					self.processed_count["skipped"] += 1

			# 情况3: 离线设备15分钟内有数据 -> 恢复在线
			elif current_status == DeviceStatus.OFFLINE.value:
				if minutes_since_last < OFFLINE_THRESHOLD_MINUTES:
					# 查询是否有未关闭的告警
					alarm_id = self.get_open_alarm_id(meter_code)

					logger.info(f"设备恢复在线: meter_code={meter_code}, "
								f"最后上报={device['last_ts']}, "
								f"距今={minutes_since_last}分钟")

					# 开启事务
					try:
						self.update_device_status(meter_code, device_type, DeviceStatus.ONLINE)

						# 如果有关联告警，关闭告警和工单
						if alarm_id:
							self.close_alarm_and_work_order(meter_code, alarm_id)
							logger.info(f"已关闭关联的告警和工单: alarm_id={alarm_id}")
						else:
							logger.warning(f"设备恢复在线但无关联告警，仅更新状态: {meter_code}")

						self.conn.commit()
					except Exception as e:
						self.conn.rollback()
						raise
					self.processed_count["to_online"] += 1
					logger.info(f"设备已恢复在线: {meter_code}")
				else:
					self.processed_count["skipped"] += 1

		except Exception as e:
			logger.error(f"处理设备失败: {meter_code}, error={e}")
			if self.conn:
				self.conn.rollback()
			self.processed_count["error"] += 1

	@tenacity.retry(
		stop=tenacity.stop_after_attempt(3),
		wait=tenacity.wait_fixed(5),
		retry=tenacity.retry_if_exception_type(Exception)
	)
	def run(self):
		"""主执行流程"""
		start_time = datetime.now()
		logger.info("=" * 80)
		logger.info(f"设备离线监控任务开始: {start_time}")
		logger.info(f"配置: 离线阈值={OFFLINE_THRESHOLD_MINUTES}分钟, 检查窗口={CHECK_WINDOW_MINUTES}分钟")
		logger.info("=" * 80)

		try:
			# 连接数据库
			self.connect_db()

			# 获取所有设备及最后上报时间
			devices = self.get_devices_with_last_telemetry()

			# 逐个处理设备
			for device in devices:
				self.process_device(device)

			# 统计信息
			end_time = datetime.now()
			duration = (end_time - start_time).total_seconds()

			logger.info("=" * 80)
			logger.info(f"设备离线监控任务完成: {end_time}")
			logger.info(f"执行时长: {duration:.2f} 秒")
			logger.info(f"统计信息:")
			logger.info(f"  - 标记为离线: {self.processed_count['to_offline']}")
			logger.info(f"  - 恢复在线: {self.processed_count['to_online']}")
			logger.info(f"  - 跳过: {self.processed_count['skipped']}")
			logger.info(f"  - 错误: {self.processed_count['error']}")
			logger.info("=" * 80)

		except Exception as e:
			logger.error(f"任务执行失败: {e}", exc_info=True)
			raise
		finally:
			self.close_db()


if __name__ == '__main__':
	monitor = DeviceOfflineMonitor()
	monitor.run()
